﻿Imports OfficeOpenXml

Public Class ExcelNeteges
    Public Shared Function Generar(Hostals As List(Of Hostal), DataInicial As Date?, DataFinal As Date?) As String
        Dim Fitxer As IO.FileInfo = New IO.FileInfo(My.Settings.RootPath & Now.Ticks & ".xlsx")

        Using xlPackage As ExcelPackage = New ExcelPackage(Fitxer)
            CrearEstils(xlPackage)

            For Each H As Hostal In Hostals
                Dim Ws As ExcelWorksheet = xlPackage.Workbook.Worksheets.Add(H.Nom)
                ExportarHostal(Ws, H, DataInicial, DataFinal)
            Next
            xlPackage.Save()
        End Using

        Return Fitxer.FullName
    End Function

    Private Shared Sub CrearEstils(xlPackage As ExcelPackage)
        Dim Estil As Style.XmlAccess.ExcelNamedStyleXml

        Estil = xlPackage.Workbook.Styles.CreateNamedStyle("Titol")
        Estil.Style.Font.Size = 14
        Estil.Style.Font.Name = "Calibri"
        Estil.Style.Font.Color.SetColor(System.Drawing.Color.DarkBlue)

        Estil = xlPackage.Workbook.Styles.CreateNamedStyle("Capcalera")
        Estil.Style.Font.Size = 12
        Estil.Style.Font.Name = "Calibri"
        Estil.Style.Font.Bold = True
        Estil.Style.VerticalAlignment = Style.ExcelVerticalAlignment.Top
        Estil.Style.HorizontalAlignment = Style.ExcelHorizontalAlignment.Center
        Estil.Style.WrapText = True

        Estil = xlPackage.Workbook.Styles.CreateNamedStyle("Data")
        Estil.Style.Font.Size = 11
        Estil.Style.Font.Name = "Calibri"
        Estil.Style.Numberformat.Format = "dd/MM/yyyy"
        Estil.Style.HorizontalAlignment = Style.ExcelHorizontalAlignment.Right

        Estil = xlPackage.Workbook.Styles.CreateNamedStyle("Temps")
        Estil.Style.Font.Size = 11
        Estil.Style.Font.Name = "Calibri"
        Estil.Style.Numberformat.Format = "hh:mm"
        Estil.Style.HorizontalAlignment = Style.ExcelHorizontalAlignment.Right

        Estil = xlPackage.Workbook.Styles.CreateNamedStyle("Moneda")
        Estil.Style.Font.Size = 11
        Estil.Style.Font.Name = "Calibri"
        Estil.Style.Numberformat.Format = "#,##0.00"

        Estil = xlPackage.Workbook.Styles.CreateNamedStyle("Total")
        Estil.Style.Font.Size = 12
        Estil.Style.Font.Name = "Calibri"
        Estil.Style.Font.Bold = True
        Estil.Style.Border.Top.Style = Style.ExcelBorderStyle.Thin
        Estil.Style.Numberformat.Format = "#,##0.00"
    End Sub


    Private Shared Sub ExportarHostal(WS As ExcelWorksheet, Hostal As Hostal, DataInicial As Date?, DataFinal As Date?)
        Const FILA_CAPCALERA As Integer = 3
        Const INICI_FILA_DADES As Integer = FILA_CAPCALERA + 1

        Using Entities As LullabyEntities = New LullabyEntities
            'Títol
            If DataInicial.HasValue Then
                WS.Cells(1, 1).Value = My.Resources.StringLibrary.Neteges & " " & Hostal.Nom & " " & NomMes(DataInicial.Value) & " " & DataInicial.Value.Year()
            Else
                WS.Cells(1, 1).Value = My.Resources.StringLibrary.Neteges & " " & Hostal.Nom
            End If
            WS.Cells(1, 1).StyleName = "Titol"

            'Capcalera
            WS.Cells(FILA_CAPCALERA, 1).Value = My.Resources.StringLibrary.Data
            WS.Cells(FILA_CAPCALERA, 2).Value = My.Resources.StringLibrary.UsuariNeteja
            WS.Cells(FILA_CAPCALERA, 3).Value = My.Resources.StringLibrary.UsuariObertura
            WS.Cells(FILA_CAPCALERA, 4).Value = My.Resources.StringLibrary.UsuariTancament
            WS.Cells(FILA_CAPCALERA, 5).Value = My.Resources.StringLibrary.HoraInici
            WS.Cells(FILA_CAPCALERA, 6).Value = My.Resources.StringLibrary.HoraFi
            WS.Cells(FILA_CAPCALERA, 7).Value = My.Resources.StringLibrary.TotalHores
            WS.Cells(FILA_CAPCALERA, 8).Value = My.Resources.StringLibrary.Observacions
            WS.Cells(FILA_CAPCALERA, 1, FILA_CAPCALERA, 16).StyleName = "Capcalera"
            WS.Row(FILA_CAPCALERA).Height = 50

            WS.View.FreezePanes(INICI_FILA_DADES, 3)

            'Amples de columna
            WS.Column(1).Width = 13
            WS.Column(2).Width = 25
            WS.Column(3).Width = 25
            WS.Column(4).Width = 25
            WS.Column(5).Width = 10
            WS.Column(6).Width = 10
            WS.Column(7).Width = 10
            WS.Column(8).Width = 50

            Dim Index As Integer = 0

            For Each Neteja As Neteja In (From N As Neteja In Entities.Neteges.Include("UsuariNeteja").Include("UsuariCreador").Include("UsuariFinalitzador") _
                                               Where _
                                                    N.IdHostal = Hostal.Id _
                                                    And (Not DataInicial.HasValue OrElse N.Data >= DataInicial) _
                                                    And (Not DataFinal.HasValue OrElse N.Data <= DataFinal) _
                                               Order By N.Data, N.HoraObertura)


                WS.Cells(INICI_FILA_DADES + Index, 1).Value = Neteja.Data
                WS.Cells(INICI_FILA_DADES + Index, 2).Value = Neteja.UsuariNeteja.Nom
                WS.Cells(INICI_FILA_DADES + Index, 3).Value = Neteja.UsuariCreador.Nom
                If Neteja.UsuariFinalitzador IsNot Nothing Then WS.Cells(INICI_FILA_DADES + Index, 4).Value = Neteja.UsuariFinalitzador.Nom
                WS.Cells(INICI_FILA_DADES + Index, 5).Value = Neteja.HoraObertura

                If Neteja.HoraTancament.HasValue Then
                    Dim Minuts As Double = (Neteja.HoraTancament.Value - Neteja.HoraObertura).TotalMinutes
                    If Minuts < 0 Then Minuts = Minuts + 24 * 60 'Per si obrim un dia i tanquem el següent
                    WS.Cells(INICI_FILA_DADES + Index, 6).Value = Neteja.HoraTancament.Value
                    WS.Cells(INICI_FILA_DADES + Index, 7).Value = Math.Round(Minuts / 15, 0) / 4
                Else
                    WS.Cells(INICI_FILA_DADES + Index, 6).Value = Nothing
                    WS.Cells(INICI_FILA_DADES + Index, 7).Value = 0
                End If

                WS.Cells(INICI_FILA_DADES + Index, 8).Value = Neteja.Observacions

                Index = Index + 1
            Next

            WS.Cells(INICI_FILA_DADES, 1, INICI_FILA_DADES + Index, 1).StyleName = "Data"
            WS.Cells(INICI_FILA_DADES, 5, INICI_FILA_DADES + Index, 6).StyleName = "Temps"
            WS.Cells(INICI_FILA_DADES, 7, INICI_FILA_DADES + Index, 7).StyleName = "Moneda"

            'Deixem una fila en blanc
            Index += 1

            If Index > 0 Then
                WS.Cells(INICI_FILA_DADES + Index, 7).Formula = String.Format("SUM({0})", New ExcelAddress(INICI_FILA_DADES, 7, INICI_FILA_DADES + Index - 1, 7).Address)
                WS.Cells(INICI_FILA_DADES + Index, 5, INICI_FILA_DADES + Index, 7).StyleName = "Total"
            End If

        End Using
    End Sub

    Private Shared Function NomDiaSetmana(Data As Date) As String
        Select Case Data.DayOfWeek
            Case DayOfWeek.Monday
                Return My.Resources.StringLibrary.Dilluns
            Case DayOfWeek.Tuesday
                Return My.Resources.StringLibrary.Dimarts
            Case DayOfWeek.Wednesday
                Return My.Resources.StringLibrary.Dimecres
            Case DayOfWeek.Thursday
                Return My.Resources.StringLibrary.Dijous
            Case DayOfWeek.Friday
                Return My.Resources.StringLibrary.Divendres
            Case DayOfWeek.Saturday
                Return My.Resources.StringLibrary.Dissabte
            Case DayOfWeek.Sunday
                Return My.Resources.StringLibrary.Diumenge
            Case Else
                Return ""
        End Select
    End Function

    Private Shared Function NomMes(Data As Date) As String
        Select Case Data.Month
            Case 1
                Return My.Resources.StringLibrary.Gener
            Case 2
                Return My.Resources.StringLibrary.Febrer
            Case 3
                Return My.Resources.StringLibrary.Mars
            Case 4
                Return My.Resources.StringLibrary.Abril
            Case 5
                Return My.Resources.StringLibrary.Maig
            Case 6
                Return My.Resources.StringLibrary.Juny
            Case 7
                Return My.Resources.StringLibrary.Juliol
            Case 8
                Return My.Resources.StringLibrary.Agost
            Case 9
                Return My.Resources.StringLibrary.Setembre
            Case 10
                Return My.Resources.StringLibrary.Octubre
            Case 11
                Return My.Resources.StringLibrary.Novembre
            Case 12
                Return My.Resources.StringLibrary.Desembre
            Case Else
                Return ""
        End Select

    End Function
End Class
